﻿
////////////////////////////////////////////////////////////////////////////////////////////
// 文件名: SysUserDAL.gen.cs
//
// 模块名称: SysUserDal
//
// 作者: LPC
//
// 历史记录（记录修改记录，修改文件后请添加修改记录，注明修改时间、修改人，修改内容）:
// 13-2-19    LPC    创建文件
////////////////////////////////////////////////////////////////////////////////////////////

using System;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using Common;
using Model;

namespace DAL
{
    /// <summary>
    /// SysUser表数据访问层
    /// </summary>
    public class SysUserDAL
    {
        /// <summary>
        /// 根据Guid字段获取SysUserInfo实体对象
        /// </summary>
        /// <param name="guid">Guid</param>
        /// <returns>SysUserInfo实体对象</returns>
        public static SysUserInfo GetInfoById(string guid)
        {
            var list = new List<SysUserInfo>();

            //创建DbCommand对象
            HIDB.dbo cmd = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@Guid",guid)    
            };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.GetDataTable("SELECT *,EmployeeName as UserRole,Pwd as OldPwd FROM SysUser WHERE Guid=@Guid", sqlparams);
                list = dt.MapToList<SysUserInfo>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }
        public static IFConfig GetIFInfoById(string id)
        {
            List<IFConfig> list = new List<IFConfig>();

            //创建DbCommand对象
            HIDB.dbo cmd = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@GUID",id)    
            };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.GetDataTable("select * from [Working_db].[IFConfig] where GUID=@GUID", sqlparams);
                list = dt.MapToList<IFConfig>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (list != null && list.Count>0)
            {
                return list[0];
            }
            return null;
        }
        public static UserIFConfig GetUserIFInfoById(string id)
        {
            List<UserIFConfig> list = new List<UserIFConfig>();

            //创建DbCommand对象
            HIDB.dbo cmd = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
             new SqlParameter("@GUID",id)    
            };

            //执行命令返回结果
            try
            {
                DataTable dt = cmd.GetDataTable("select * from [Working_db].[UserIFConfig] where GUID=@GUID", sqlparams);
                list = dt.MapToList<UserIFConfig>();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            if (list != null && list.Count > 0)
            {
                return list[0];
            }
            return null;
        }
        public static List<string> GetUserColumnList()
        {
            HIDB.dbo cmd = HIDB.common.make();
            try
            {
                List<string> list = new List<string>();
                DataTable dt = cmd.GetDataTable("select * from [dbo].[SysUser] where 1<>1");
                foreach (DataColumn dc in dt.Columns)
                {
                    list.Add(dc.ColumnName);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 往SysUser表内添加新记录
        /// </summary>
        /// <param name="sysUserInfo">SysUserInfo实体对象</param>
        /// <returns>添加记录索引值</returns>
        public static int Insert(SysUserInfo sysUserInfo)
        {
            //创建cmd对象
            HIDB.dbo cmd = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = {
            new SqlParameter("@Guid",sysUserInfo.Guid),
            new SqlParameter("@EmployeeCode",sysUserInfo.EmployeeCode),
            new SqlParameter("@EmployeeName",sysUserInfo.EmployeeName), 
            new SqlParameter("@OfficeTel",sysUserInfo.OfficeTel ?? "" ),
            new SqlParameter("@HomeTel",sysUserInfo.HomeTel ?? "" ),
            new SqlParameter("@Mobile",sysUserInfo.Mobile ?? "" ),
            new SqlParameter("@Gender",sysUserInfo.Gender ?? "" ),
            new SqlParameter("@OfficeEmail",sysUserInfo.OfficeEmail ?? "" ),
            new SqlParameter("@Username",sysUserInfo.Username),
            new SqlParameter("@Pwd",sysUserInfo.Pwd),
            new SqlParameter("@CreateUser",sysUserInfo.CreateUser ?? "" ),
            new SqlParameter("@CreateTime",sysUserInfo.CreateTime),
            new SqlParameter("@Status",sysUserInfo.Status), 
            new SqlParameter("@CompanyName",sysUserInfo.CompanyName ?? "" ),
            new SqlParameter("@Department",sysUserInfo.Department ?? "" ),
            new SqlParameter("@Post",sysUserInfo.Post ?? "" ),
            new SqlParameter("@UserType",sysUserInfo.UserType)
            };

            //执行命令返回结果
            try
            {
                cmd.ExecuteSql(@"INSERT INTO dbo.SysUser (Guid,EmployeeCode,EmployeeName,OfficeTel,HomeTel,Mobile,Gender,OfficeEmail,RTXID,Username,CompanyName,Department,Post,Pwd,CreateUser,CreateTime,Status,UserType) 
VALUES(@Guid,@EmployeeCode,@EmployeeName,@OfficeTel,@HomeTel,@Mobile,@Gender,@OfficeEmail,@Username,@Username,@CompanyName,@Department,@Post,[dbo].[GetMD5](@Pwd,lower(@username)),@CreateUser,@CreateTime,@Status,@UserType)", sqlparams);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static int InsertIF(IFConfig info)
        {
            //创建cmd对象
            HIDB.dbo cmd = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = {
            new SqlParameter("@GUID",info.GUID),
            new SqlParameter("@IFSysName",info.IFSysName),
            new SqlParameter("@IFName",info.IFName),
            new SqlParameter("@MsgContent",info.MsgContent ?? "" ), 
            new SqlParameter("@Extensions",info.Extensions ?? "" ), 
            new SqlParameter("@IColumnName",info.IColumnName ), 
            new SqlParameter("@MinInterval",info.MinInterval ) 
            };

            //执行命令返回结果
            try
            {
                cmd.ExecuteSql(@"INSERT INTO [Working_db].IFConfig (GUID,IFSysName,IFName,MsgContent,Extensions,IColumnName,MinInterval,CreateTime) 
VALUES(@GUID,@IFSysName,@IFName,@MsgContent,@Extensions,@IColumnName,@MinInterval,getdate())", sqlparams);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int UpdateUserIF(List<UserIFConfig> info, string modelID)
        {
            HIDB.dbo cmd = HIDB.common.make();
            try
            {
                DataTable dt = Common.DBConvert.ConvertToDataTable(info);
                IDataParameter[] sqlparams = {
                new SqlParameter("@modelID",modelID),
                new SqlParameter("@UserIFConfig",dt) 
                };

                cmd.StoredUpdate("[Working_db].[UpdateUserIFConfig]", sqlparams);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int InsertUserIFColumn(UserIFColumnConfig info)
        {
            //创建cmd对象
            HIDB.dbo cmd = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = { 
            new SqlParameter("@ITableID",info.ITableID),
            new SqlParameter("@IColumnGUID",info.IColumnGUID), 
            new SqlParameter("@IColumnText", info.IColumnText) 
            };

            //执行命令返回结果
            try
            {
                cmd.ExecuteSql(@"INSERT INTO [Working_db].UserIFColumnConfig
select newid(),@ITableID,@IColumnGUID,@IColumnText,getdate()", sqlparams);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static int UpdateIF(IFConfig info)
        {
            //创建cmd对象
            HIDB.dbo cmd = HIDB.common.make();

            //添加输入输出参数
            IDataParameter[] sqlparams = {
            new SqlParameter("@GUID",info.GUID),
            new SqlParameter("@IFSysName",info.IFSysName),
            new SqlParameter("@IFName",info.IFName),
            new SqlParameter("@MsgContent",info.MsgContent==null ? "" : info.MsgContent), 
            new SqlParameter("@Extensions",info.Extensions==null ? "" : info.Extensions), 
            new SqlParameter("@IColumnName",info.IColumnName ), 
            new SqlParameter("@MinInterval",info.MinInterval==null ? 1 : info.MinInterval) 
            };

            //执行命令返回结果
            try
            {
                cmd.ExecuteSql(@"UPDATE [Working_db].IFConfig set IFSysName=@IFSysName,IFName=@IFName,MsgContent=@MsgContent,Extensions=@Extensions,IColumnName=@IColumnName,MinInterval=@MinInterval where GUID=@GUID", sqlparams);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 更新SysUser表记录
        /// </summary>
        /// <param name="sysUserInfo">SysUserInfo实体对象</param>
        public static int Update(SysUserInfo sysUserInfo)
        {
            int num = -1;

            HIDB.dbo cmd = HIDB.common.make();

            IDataParameter[] sqlparams = {
            new SqlParameter("@Guid",sysUserInfo.Guid),
            new SqlParameter("@EmployeeCode",sysUserInfo.EmployeeCode ),
            new SqlParameter("@EmployeeName",sysUserInfo.EmployeeName ),
            new SqlParameter("@Username",sysUserInfo.Username ),
            new SqlParameter("@EmployeeID",sysUserInfo.EmployeeID ?? "" ),
            new SqlParameter("@OfficeTel",sysUserInfo.OfficeTel ?? "" ),
            new SqlParameter("@HomeTel",sysUserInfo.HomeTel ?? "" ),
            new SqlParameter("@Mobile",sysUserInfo.Mobile ?? "" ),
            new SqlParameter("@Gender",sysUserInfo.Gender ?? "" ),
            new SqlParameter("@OfficeEmail",sysUserInfo.OfficeEmail ?? "" ), 
            new SqlParameter("@CompanyName",sysUserInfo.CompanyName ?? "" ),
            new SqlParameter("@Department",sysUserInfo.Department ?? "" ),
            new SqlParameter("@Post",sysUserInfo.Post ?? "" ),
            new SqlParameter("@Pwd",sysUserInfo.Pwd ?? "" ),
            new SqlParameter("@OldPwd",sysUserInfo.OldPwd ?? "" ), 
            new SqlParameter("@Status",sysUserInfo.Status) 
                                         };

            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("[dbo].[UP_SysUser_Update]", sqlparams);
                num = 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return num;
        }

        /// <summary>
        /// 根据Guid字段删除SysUserInfo表信息
        /// </summary>
        /// <param name="guid">Guid</param>
        public static int Delete(IDataParameter[] par)
        {
            HIDB.dbo cmd = HIDB.common.make();
            try
            {
                cmd.ExecuteSql("delete  FROM dbo.SysUser where guid=@Guid", par);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static int DeleteIF(IDataParameter[] par)
        {
            HIDB.dbo cmd = HIDB.common.make();
            try
            {
                cmd.ExecuteSql("delete from [Working_db].[IFConfig] where GUID=@GUID", par);
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static int UpdateFunStatus(List<SqlParameter> par)
        {
            HIDB.dbo cmd = HIDB.common.make();
            try
            {
                DataTable dt = cmd.GetDataTable("select Status from [Working_db].[IFConfig] where guid=@GUID", par.ToArray());
                int status = 1;
                if (dt.Rows[0][0].ToString() == "1")
                    status = 0;
                par.Add(new SqlParameter("@Status", status));
                cmd.ExecuteSql("update [Working_db].[IFConfig] set Status=@Status where GUID=@GUID", par.ToArray());
                return 1;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int CheckUserName(string userName)
        {
            HIDB.dbo cmd = HIDB.common.make();
            try
            {
                IDataParameter[] paras = new SqlParameter[1];
                paras[0] = new SqlParameter("@UserName", userName);
                DataTable dt = cmd.StoredDataTable("[dbo].[CheckUserName]", paras);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return Convert.ToInt32(dt.Rows[0][0]);
                }
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_RoleInfo_Insert", "UP_Sys_RoleInfo_Insert", 0, 1, ex, "", "");
            }
            return 0;
        }

        public static string GetOldPwd(string userName)
        {
            string result = string.Empty;
            try
            {
                HIDB.dbo cmd = HIDB.common.make();
                SqlParameter[] sqlparams = new SqlParameter[]
                {
                    new SqlParameter("@Username",userName)
                };
                result = cmd.GetScalar("select [Pwd]  from [dbo].[SysUser] where username =@Username", sqlparams);
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

        public static int ChangePassword(string userName, string password, string oldPwd)
        {
            int result = -1;
            try
            {
                HIDB.dbo cmd = HIDB.common.make();
                SqlParameter[] sqlparams = new SqlParameter[]
                {
                    new SqlParameter("@Username",userName)
                   ,new SqlParameter("@PassWord",password)
                   ,new SqlParameter("@oldPwd",oldPwd)
                };
                DataTable dt = cmd.StoredDataTable("[dbo].[UP_SysUser_ChangePassword]", sqlparams);
                if (dt != null && dt.Rows.Count > 0)
                {
                    result = Convert.ToInt32(dt.Rows[0][0]);
                }
                else
                    result = 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }
            return result;
        }

        /// <summary>
        /// 根据实体获取SysUserInfo实体List对象
        /// </summary>
        /// <returns>SysUserInfo实体对象</returns>
        public static IList<SysUserInfo> GetList(SqlParameter[] pare)
        {
            IList<SysUserInfo> list = new List<SysUserInfo>();
            HIDB.dbo cmd = HIDB.common.make();
            try
            {
                DataTable dt = cmd.StoredDataTable("dbo.UP_SysUser_GetList", pare);
                list = dt.MapToList<SysUserInfo>();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据实体获取SysUserInfo实体List对象
        /// </summary>
        /// <returns>SysUserInfo实体对象</returns>
        public IList<IFConfig> GetIFList(out int totalCount)
        {
            totalCount = 0;
            List<IFConfig> result = new List<IFConfig>();
            try
            {
                HIDB.dbo cmd = HIDB.common.make();
                List<SqlParameter> cmdParms = new List<SqlParameter>();
                string strSql = @"SELECT * FROM [Working_db].[IFConfig] ";
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<IFConfig>();
                totalCount = result.Count;
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public string GetRTXSysName()
        {  
            try
            {
                HIDB.dbo cmd = HIDB.common.make();
                string strSql = @"select IFSysName from [Working_db].[IFConfig] where IFName='RTX' and Status=1";
                DataTable dt = cmd.GetDataTable(strSql);
                if (dt != null && dt.Rows.Count > 0)
                    return dt.Rows[0][0].ToString();
                else
                    return "";
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据实体获取SysUserInfo实体List对象
        /// </summary>
        /// <returns>SysUserInfo实体对象</returns>
        public IList<UserIFConfig> GetUserIFList(string IFunName, string DBName, out int totalCount)
        {
            totalCount = 0;
            List<UserIFConfig> result = new List<UserIFConfig>();
            try
            {
                HIDB.dbo cmd = HIDB.common.make();
                List<SqlParameter> cmdParms = new List<SqlParameter>();
                string strSql = @"SELECT * FROM [Working_db].[UserIFConfig] WHERE 1=1";
                if (!string.IsNullOrEmpty(IFunName))
                {
                    strSql += " AND ITable like '%'+@ITable+'%'";
                    cmdParms.Add(new SqlParameter("@ITable", IFunName));
                }
                if (!string.IsNullOrEmpty(DBName))
                {
                    strSql += " AND DBName = @DBName";
                    cmdParms.Add(new SqlParameter("@DBName", DBName));
                }

                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<UserIFConfig>();
                totalCount = result.Count;
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int UpdateState(IDataParameter[] paras)
        {
            int num = -1;

            //创建Database对象          
            HIDB.dbo cmd = HIDB.common.make();
            //执行命令返回结果
            try
            {
                cmd.StoredUpdate("UP_SysUser_UpdateState", paras);
                num = 1;
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_RoleInfo_Insert", "UP_Sys_RoleInfo_Insert", 0, 1, ex, "", "");
            }
            return num;
        }

        public static bool GetDataByUsername(IDataParameter[] par)
        {
            bool flg = false;
            //创建DbCommand对象
            HIDB.dbo cmd = HIDB.common.make();
            //执行命令返回结果
            try
            {
                DataTable dt = cmd.StoredDataTable("dbo.up_getUserByUserName", par);
                if (dt.Rows.Count > 0)
                {
                    flg = true;
                }

            }
            catch (Exception ex)
            {
                Logger.LogError("dbo.up_getUserByUserName", "dbo.up_getUserByUserName", 0, 1, ex, "", "");
            }
            return flg;
        }

        public static List<Sys_RoleInfo> GetLogin(SqlParameter[] params1)
        {
            HIDB.dbo cmd = HIDB.common.make();
            List<Sys_RoleInfo> list = new List<Sys_RoleInfo>();
            try
            {
                DataTable dt = cmd.StoredDataTable("dbo.UP_SysUser_GetLogin", params1);
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        Sys_RoleInfo m = new Sys_RoleInfo();
                        m.RoleID = dr[0].ToString();
                        m.RoleName = dr[2].ToString();
                        m.Description = dr[1].ToString();
                        list.Add(m);
                    }
                }
                return list;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static SysUserInfo GetInfoByGUID(string guid)
        {
            SysUserInfo userInfo = null;

            //创建DbCommand对象
            HIDB.dbo cmd = HIDB.common.make();
            //执行命令返回结果
            try
            {
                //添加输入输出参数
                IDataParameter[] sqlparams = { 
                    new SqlParameter("@Guid",guid)    
                };
                string strSql = "SELECT *,EmployeeName as UserRole FROM SysUser WHERE Guid=@Guid";
                DataTable dt = cmd.GetDataTable(strSql, sqlparams);
                var list = dt.MapToList<SysUserInfo>();
                if (list != null && list.Count > 0)
                {
                    userInfo = list[0];
                }
            }
            catch (Exception ex)
            {
                Logger.LogError("UP_Sys_SysUserInfo_Insert", "UP_Sys_SysUserInfo_Insert", 0, 1, ex, "", "");
            }

            return userInfo;
        }

        #region SysUser

        public static List<SysUserInfo> GetByUsernameWithOutId(string username, string guid)
        {
            List<SysUserInfo> result = new List<SysUserInfo>();
            try
            {
                string strSql = string.Format(@"SELECT * FROM [dbo].[{0}]
  WHERE 1=1", "SysUser");
                List<IDataParameter> cmdParms = new List<IDataParameter>();

                strSql += " AND [Username] =@Username";
                cmdParms.Add(new SqlParameter("@Username", username));
                if (!string.IsNullOrEmpty(guid))
                {
                    strSql += " AND Guid!=@Guid";
                    cmdParms.Add(new SqlParameter("@Guid", guid));
                }
                HIDB.dbo cmd = HIDB.common.make();
                DataTable dt = cmd.GetDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<SysUserInfo>();
            }
            catch (Exception ex)
            {
                Logger.LogError(string.Format("[dbo].[{0}]", "SysUser"), "GetByUsernameWithOutId", 0, 1, ex, "", "");
            }
            return result;

        }



        public static List<SysUserInfo> GetUserList(string EmployeeCode, string EmployeeName, string UserName, string expIDs
            , string order, string sort
            , string rows, string page, out int recordCount)
        {
            recordCount = 0;
            List<SysUserInfo> result = new List<SysUserInfo>();
            try
            {
                HIDB.dbo cmd = HIDB.common.make();
                List<SqlParameter> cmdParms = new List<SqlParameter>() { 
                    new SqlParameter("@EmployeeCode",EmployeeCode), 
            new SqlParameter("@EmployeeName",EmployeeName), 
            new SqlParameter("@UserName",UserName), 
            new SqlParameter("@ExpIDs",expIDs),  
            new SqlParameter("@Order",order),
            new SqlParameter("@Sort",sort),
            new SqlParameter("@Rows",rows),
            new SqlParameter("@PageNum",page)
                };
                string strSql = string.Format(@"dbo.UP_SysUser_GetList");
                DataTable dt = cmd.StoredDataTable(strSql, cmdParms.ToArray());
                result = dt.MapToList<SysUserInfo>();
                recordCount = result.Count;
                if (result.Count > 0)
                    recordCount = result[0].Count;
            }
            catch (Exception ex)
            {
                Logger.LogError("dbo.UP_SysUser_GetList", "GetUserList", 0, 1, ex, "", "");
            }
            return result;
        }
        #endregion

    }
}